﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PBOffice.Models;
using System.Data.SqlClient;
using System.Data;

namespace PBOffice.DAL
{
    public class DeptService
    {
        public List<Department> GetAllDepartment()
        {
            string sql = "select [DeptId],[DeptName],[DeptCode],b.[BranchId],b.[BranchName], b.[BranchShortName],[Description],[ParentId],d.Status from Department d,Branch b where d.BranchId=b.BranchId";
            List<Department> list = new List<Department>();
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Department dept = new Department();
                    Branch ba = new Branch();
                    dept.DeptId = Convert.ToInt32(dr["DeptId"]);
                    dept.DeptName = dr["DeptName"].ToString();
                    dept.DeptCode = dr["DeptCode"].ToString();
                    dept.Description = dr["Description"].ToString();

                    ba.BranchId = Convert.ToInt32(dr["BranchId"]);
                    // ba.BranchName = dr["BranchName"].ToString();
                    ba.BranchShortName = dr["BranchShortName"].ToString();
                    dept.Branch = ba;
                    dept.ParentId = Convert.ToInt32(dr["ParentId"]);
                    dept.Status = (byte)dr["Status"];
                    list.Add(dept);
                }
            }
            return list;
        }
        public void AddDept(Department dep)
        {
            string sql = "insert [Department] (DeptName,DeptCode,BranchId,Description,ParentId,Status) values(@DeptName,@DeptCode,"
            + "@BranchId,@Description,@ParentId,@Status)";
            SqlParameter[] spa = { new SqlParameter("@DeptName",dep.DeptName),
                                     new SqlParameter("@BranchId",dep.Branch.BranchId),
                                     new SqlParameter("@Description",dep.Description),
                                     new SqlParameter("@DeptCode",dep.DeptCode),
                                     new SqlParameter("@ParentId",dep.ParentId),
                                     new SqlParameter("@Status",dep.Status)
                                 };
            SqlHelp.ExecuteNonQuery(SqlHelp.connectionstring, CommandType.Text, sql, spa);
        }
        public int GetIdByDeptName(string name)
        {
            string sql = string.Format("select [DeptId] from Department where DeptName='{0}'", name);
            int deptId = 0;
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    deptId = (int)dr["DeptId"];
                }
            }
            return deptId;
        }
        public int GetCountByParentId(int id)
        {
            string sql = string.Format("select count(*) from Department where ParentId={0}", id);
            return Convert.ToInt32(SqlHelp.ExecuteScalar(SqlHelp.connectionstring, CommandType.Text, sql));
        }
        public List<Department> GetAllDeptByParentId(int id)
        {
            string sql = string.Format("select [DeptId],[DeptName],[DeptCode],b.[BranchId],b.[BranchName], b.[BranchShortName],[Description],[ParentId],d.Status from Department d,Branch b where d.BranchId=b.BranchId and ParentId={0}", id);
            List<Department> list = new List<Department>();
            using (SqlDataReader dr = SqlHelp.ExecuteReader(SqlHelp.connectionstring, CommandType.Text, sql))
            {
                while (dr.Read())
                {
                    Department dept = new Department();
                    Branch ba = new Branch();
                    dept.DeptId = Convert.ToInt32(dr["DeptId"]);
                    dept.DeptName = dr["DeptName"].ToString();
                    dept.DeptCode = dr["DeptCode"].ToString();
                    dept.Description = dr["Description"].ToString();

                    ba.BranchId = Convert.ToInt32(dr["BranchId"]);
                    // ba.BranchName = dr["BranchName"].ToString();
                    ba.BranchShortName = dr["BranchShortName"].ToString();
                    dept.Branch = ba;
                    dept.ParentId = Convert.ToInt32(dr["ParentId"]);
                    dept.Status = (byte)dr["Status"];
                    list.Add(dept);
                }
            }
            return list;
        }

    }

}
